﻿using System;
using System.Data;
using System.Collections;
using System.Collections.Specialized;
using System.Text;
using System.Drawing;

namespace Tab2Sql
{
    internal partial class DataTable2SQL
    {
        /// <summary>
        /// Returns a SQL Precision, Scale friendly column width. 
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        private string getColumnScale(decimal p)
        {
            int Scale = 14;
            int Precision = 5;

            decimal billion = 1000*1000*1000;
            if (p > billion)
            {
                decimal answer = p/billion;
                int tmpx = Convert.ToInt16(answer);
                Scale = Scale + tmpx;
            }
            decimal WholeNumber = Math.Floor(p);
            decimal LeftOver = p - WholeNumber;
            Precision = Math.Max(LeftOver.ToString().Replace("0.", String.Empty).Length, 2);
            string rtnString = "(" + Scale + "," + Precision + ")";
            return rtnString;
        }

        private void doDebugMessage(bool isDebugOn, string cName, DataTypes cType)
        {
            //redundant code.

            int columnLen = 33;
            EndS.Append(DGen.DText.Replicate(" ", columnLen));
            EndS.Append("/*" + cName + " - " + cType + @"  */  ");
        }

        private void doWhiteSpace(bool lotsOfWhiteSpace)
        {
            if (lotsOfWhiteSpace == false)
            {
                EndS.Append(Environment.NewLine + "\t\t");
            }
        }

        private void doAdditionalColumns(string myTab)
        {
            //TODO : DEPRECEATED 11/10/2009 12:50:55
            //To be removed probably. Currently offers ability to add two columns in at the users request. 
            if (MyOptions.AddCol1Name.Length > 0)
            {
                EndS.Append(Environment.NewLine + myTab + myTab + MyOptions.AddCol1Name + myTab +
                            MyOptions.AddCol1DataType + ",");
            }
            if (MyOptions.AddCol2Name.Length > 0)
            {
                EndS.Append(Environment.NewLine + myTab + myTab + MyOptions.AddCol2Name + myTab +
                            MyOptions.AddCol2DataType + ",");
            }
        }


        //*************************************************************************************************************************************************
        //*************************************************************************************************************************************************
        private void doHeader()
        {
            if (sqlMode == SqlStatementType.DataMode)
            {
                return;
            }

            if (sqlVariant == SQLVariants.TSql)
            {
                EndS.AppendLine("-- SqlVariant : TSql");
                EndS.AppendLine("{{ BATCHES_TEXT }}");
                EndS.AppendLine(sqlFragments.dropTable(MyOptions.TableName));
                EndS.AppendLine(sqlFragments.setnocount());
                EndS.AppendLine("GO");
                EndS.AppendLine(sqlFragments.setDateFormat());
                EndS.AppendLine("GO");
                EndS.Append(Environment.NewLine);
            }
            else if (sqlVariant == SQLVariants.SqlAnywhere)
            {
                EndS.AppendLine("-- SqlAnywhere 9.02 Format" + Environment.NewLine + Environment.NewLine);
                EndS.AppendLine(sqlFragments.dropTable(MyOptions.TableName,sqlVariant));
                EndS.AppendLine("GO");
            }
            else if (sqlVariant != SQLVariants.TSql && sqlVariant != SQLVariants.SqlAnywhere)
            {
                EndS.AppendLine("-- ANSI SQL" + Environment.NewLine + Environment.NewLine);
            }
        }


        /// <summary>
        /// This replaces any placeholder text with real values.
        /// </summary>
        private void applyTemplates()
        {
           
            StringDictionary templateDict = getTemplateDictionary();

            foreach (System.Collections.DictionaryEntry templateEntry in templateDict)
            {
                // you have to force everything to upper case because elsewhere the text will have been forced to lower case. Keeping the templates as uppercase as it's more consistent with other template systems.
                string p = templateEntry.Key.ToString().ToUpperInvariant();

                string q = templateEntry.Value.ToString();
                EndS.Replace(p, q);
            }
            
        }

        private StringDictionary getTemplateDictionary()
        {
            StringDictionary templateDict = new StringDictionary();
            string batchText = string.Empty;
            string collectiveNoun="";
            if (batchCount == 1)
            {
                collectiveNoun = "Batch";
            }
            else
            {
                collectiveNoun = "Batches";
            }
            batchText =  "-- " + batchCount.ToString() +" "+ collectiveNoun + Environment.NewLine;
            templateDict.Add("{{ BATCHES_TEXT }}", batchText);

            return templateDict;
        }

        //*************************************************************************************************************************************************
        //*************************************************************************************************************************************************
        private void doFooter()
        {
            if (MyOptions.IncludeQueryFooter == false && sqlVariant==SQLVariants.TSql)
            {
                EndS.AppendLine("GO");
                EndS.AppendLine("SET NOCOUNT OFF");
                EndS.AppendLine("GO");
                EndS.AppendLine("SELECT TOP 10 PERCENT * FROM " + MyOptions.TableName + ";");
                return;
            }
            if (MyOptions.IncludeQueryFooter == false && sqlVariant == SQLVariants.SqlAnywhere)
            {
        
                EndS.AppendLine("GO");
                EndS.AppendLine("SELECT  * FROM " + MyOptions.TableName + ";");
                return;
            }
            if(sqlVariant == SQLVariants.SQLite)
            {
               EndS.AppendLine("SELECT * FROM " + MyOptions.TableName + ";");
            }

            EndS.AppendLine(getAggregateSQL());
        }

        private string getSampleRows()
        {
            return "SELECT TOP 1000 * FROM " + MyOptions.TableName + ";";
        }

        private string getDupeCheckSQL()
        {
            DataTable dt = ourDataTable;
            string keyColumn = dt.Columns[0].ColumnName;
            string columnList = "";
            foreach (DataColumn dc in dt.Columns)
            {
                string cn = dc.ColumnName;
                columnList += cn + ",";
            }
            columnList = columnList.TrimEnd(',');

            var sqlStatement = new StringBuilder();

            sqlStatement.AppendLine("SELECT");
            sqlStatement.Append("\t" + columnList);
            sqlStatement.AppendLine(" ,COUNT(*) as Occurs ");
            sqlStatement.AppendLine(" FROM " + MyOptions.TableName);
            sqlStatement.AppendLine(" GROUP BY" + columnList);
            sqlStatement.AppendLine(" HAVING COUNT(*)>1 ");
            sqlStatement.AppendLine(" ORDER BY COUNT(*) DESC ");

            return sqlStatement.ToString();
        }

        private string getAggregateSQL()
        {
            DataTable dt = ourDataTable;
            string keyColumn = dt.Columns[0].ColumnName;
            string columnList = "'Largest' as [Description], ";
            string columnListB = "'Smallest',";
            string columnListC = "'Unique',";
            string columnListD = "'Count',";
            string columnListE = "'Longest',";
            string columnListF = "'Shortest',";

            foreach (DataColumn dc in dt.Columns)
            {
                // we cast in the below sql because we don't know here what datatype the MAX or MIN columns will be returning. We could test for this but it's not really worth it yet.
                string cn = dc.ColumnName;
                columnList += "CAST(MAX(" + cn + ") AS VARCHAR(100)) as " + cn + ",";
                columnListB += "CAST(MIN(" + cn + ") AS VARCHAR(100)) as " + cn + ",";
                columnListC += "CAST(COUNT(DISTINCT " + cn + ") AS VARCHAR(100)) as " + cn + ",";
                columnListD += "CAST(COUNT( " + cn + ") AS VARCHAR(100)) as " + cn + ",";
                columnListE += "CAST(MAX(LEN(RTRIM(CAST( " + cn + " AS VARCHAR(100))))) AS VARCHAR(100)) as " + cn + ",";
                columnListF += "CAST(MIN(LEN(RTRIM(CAST( " + cn + " AS VARCHAR(100))))) AS VARCHAR(100)) as " + cn + ",";
            }
            columnList = columnList.TrimEnd(',');
            columnListB = columnListB.TrimEnd(',');
            columnListC = columnListC.TrimEnd(',');
            columnListD = columnListD.TrimEnd(',');
            columnListE = columnListE.TrimEnd(',');
            columnListF = columnListF.TrimEnd(',');
            var sqlStatement = new StringBuilder();

            sqlStatement.AppendLine("SELECT");
            sqlStatement.Append("\t" + columnList);
            sqlStatement.AppendLine(" ,COUNT(*) as Rows ");
            sqlStatement.AppendLine(" FROM " + MyOptions.TableName);
            sqlStatement.AppendLine("UNION ALL");
            sqlStatement.AppendLine("SELECT");
            sqlStatement.Append("\t" + columnListB);
            sqlStatement.AppendLine(" ,COUNT(*) as Rows ");
            sqlStatement.AppendLine(" FROM " + MyOptions.TableName);
            sqlStatement.AppendLine("UNION ALL");
            sqlStatement.AppendLine("SELECT");
            sqlStatement.Append("\t" + columnListC);
            sqlStatement.AppendLine(" ,COUNT(*) as Rows ");
            sqlStatement.AppendLine(" FROM " + MyOptions.TableName);
            sqlStatement.AppendLine("UNION ALL");
            sqlStatement.AppendLine("SELECT");
            sqlStatement.Append("\t" + columnListD);
            sqlStatement.AppendLine(" ,COUNT(*) as Rows ");
            sqlStatement.AppendLine(" FROM " + MyOptions.TableName);
            sqlStatement.AppendLine("UNION ALL");
            sqlStatement.AppendLine("SELECT");
            sqlStatement.Append("\t" + columnListE);
            sqlStatement.AppendLine(" ,COUNT(*) as Rows ");
            sqlStatement.AppendLine(" FROM " + MyOptions.TableName);
            sqlStatement.AppendLine("UNION ALL");
            sqlStatement.AppendLine("SELECT");
            sqlStatement.Append("\t" + columnListF);
            sqlStatement.AppendLine(" ,COUNT(*) as Rows ");
            sqlStatement.AppendLine(" FROM " + MyOptions.TableName);
            SqlSnippets dx = new SqlSnippets(@"C:\Me\PWK\tab2sql\tab2sql\Resources\SqlSnippets.xml", "b227a7d1-bf9b-4011-88cc-87e58cb481df",MyOptions.TableName);
            sqlStatement.AppendLine(dx.NameList);
            return sqlStatement.ToString();
        }

        public System.Windows.Forms.RichTextBox theRtb;
        // This is the "body" of the table.
        private void generateDataOutput()
        {
                theRtb=              new System.Windows.Forms.RichTextBox();
                MyObject.ColCount = ourDataTable.Columns.Count;
                int RowNo = 0;
                var x = new string[MyObject.ColCount];
                var prefix = new StringBuilder();
                prefix.AppendLine("");
                foreach (DataRow r in ourDataTable.Rows)
                {
                    RowNo++;
                    var dtype = new DataTypes[MyObject.ColCount];
                    System.Drawing.Color mColour = new System.Drawing.Color();
                    mColour = System.Drawing.Color.Fuchsia;
                    if (MyOptions.IncludeRowNumber)
                    {
                        mColour = System.Drawing.Color.Blue;
                        theRtb.SelectionColor = mColour;
                        var fBold = new System.Drawing.Font("Microsoft Sans-Serif", 7.25F, FontStyle.Regular);
                        theRtb.SelectionFont = fBold;
                        theRtb.SelectedText += RowNo.ToString() + "\t"; 
                    }
                    mColour = System.Drawing.Color.Fuchsia;
                    for (int i = 0; i < MyObject.ColCount; i++)
                    {
                        try
                        {
                            x[i] = r.ItemArray.GetValue(i).ToString();
                            dtype[i] = MyObject.ColumnTypes[i];
                        }
                        catch (Exception ex)
                        {
                            ErrorLog.Add(ex.Message);
                        }
                    }
                    int iCount = 0;
                foreach (string column_value in x)
                {
                        string tmpHolder = column_value;
                        if (column_value == null || column_value.Length == 0)
                        {
                            tmpHolder = "";
                            tmpHolder+=tmpHolder + "\t";
                        }
                        else
                        {
                            if (dtype[iCount] == DataTypes.percentage)
                            {
                                mColour = System.Drawing.Color.Red;
                                tmpHolder = tmpHolder.Replace("%", String.Empty);
                                try
                                {
                                    Decimal q = Convert.ToDecimal(tmpHolder);
                                    q = q / 100;
                                    tmpHolder = q.ToString();
                                }
                                catch (Exception ex)
                                {
                                    ErrorLog.Add("Error converting a percentage value.");
                                    ErrorLog.Add(ex.Message);
                                }
                            }
                            if (dtype[iCount] == DataTypes.integer)
                            {
                                mColour = System.Drawing.Color.Black;
                                
                            }
                            if (dtype[iCount] == DataTypes.numeric)
                            {
                                mColour = System.Drawing.Color.Blue;
                                tmpHolder = DGen.DText.CleanNumeric(tmpHolder);
                            }
                            if (dtype[iCount] == DataTypes.datetime)
                            {
                                mColour = System.Drawing.Color.Green;
                                tmpHolder = DGen.DText.Convert2UKDate(tmpHolder);
                            }
                            if (tmpHolder.Contains("'"))
                            {
                                tmpHolder = DGen.DText.RemoveUnescapedApostrophes(tmpHolder);
                            }
                            if (dtype[iCount] == DataTypes.varchar)
                            {
                                mColour = System.Drawing.Color.MediumPurple;
                            }
                             theRtb.SelectionColor = mColour;
                            var fBold = new System.Drawing.Font("Microsoft Sans-Serif", 7.25F, FontStyle.Regular);
                             theRtb.SelectionFont = fBold;
                             theRtb.SelectedText += tmpHolder + "\t";
                        }
                        iCount++;
                } // end of foreach column
                theRtb.SelectedText += Environment.NewLine;
            } // end of foreach row

            
            
        }

        





    }
}